/**
L * Context.java
 * 
 * @author david
 * 
 */
package org.puresilk.android.db;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;
import java.util.Map.Entry;

import org.puresilk.android.util.Util;
import org.puresilk.android.util.Logger;

import android.content.ContentValues;

/**
 * Provides a context for queries inside 
 * a SQLite db.  E.g. defines a table, 
 * view, etc. as a target data source.
 * 
 * Also defines lots of SQLite specific
 * DDL and SQL.  Some of this may need
 * attention if Android changes under-
 * lying sql server.   
 */
public class Context {
	private static Logger logger = Logger.getInstance("Db Context");
	
	// Entity properties 
	private String entity = null;
	private String [] props = null;
	private ArrayList<String> colNdx = null;
	private ColMeta [] rowMeta = null;
	
	// SQLite Comparator checks
	public static final String DEFAULT_COMP = "=";
	public static final String SQL_COMPARATORS = "!=<>";
	
    // SSQLite DDL/SQL Fragments -->
	public static final String DDL_DROP = "Drop Table If Exists %1$s";
	public static final String DDL_CREATE = "Create Table If Not Exists %1$s (%2$s)";
	public static final String SQL_INSERT = "Insert into %1$ (%2$s) values (%3$s)";
	public static final String SQL_UPDATE = "Update %1$s set %2$s %3$s";
	public static final String SQL_SELECT = "Select %1$s %2$s from %3$s %4$s %5$s %6$s %7$s";
	public static final String SQL_DELETE = "Delete from %1$s %2$s";
	public static final String SQL_WHERE = "Where %2$1d";
	public static final String SQL_GROUP_BY = "Group by %1$s";
	public static final String SQL_HAVING = "Having %1$s (%2$s)";
	public static final String SQL_ORDER_BY = "Order by %1$s";

	// SQLite Date / Time formats 
	public static final String SQL_DATE = "MM/dd/yyyy";
	public static final String SQL_TIME = "KK:MM a";
	
	/**
	 * The little ol worker class where
	 * all the magic takes place.
	 * 
	 * @author david
	 */
	public class ColMeta {
		// parsers and formatters
		private SimpleDateFormat dtfmt = null; 
		private SimpleDateFormat tmfmt = null;
		private static final String SIZED_FIELD_PARSE = "([^\\d]*)([\\d]+)";
		private static final String SIZED_FIELD_FORMAT = "$1($2)";
		
		// SQLite auto increment keys
		private static final String FMT_AUTOKEY = "INTEGER PRIMARY KEY";
		// SQLite Psuedo-Column Types
		private static final String FMT_STRING = "'%1$s'";
		private static final String FMT_NUMBER = "%1$d";
		private static final String FMT_DECIMAL = "%1$4.2d";
		private static final String FMT_DATE = "'%1$\'";
		private static final String FMT_TIME = "'%1$'";
		private static final String FMT_DATETIME = "'%1$ %1$s'";

		// SQLite table definition needs (all nested up) 
		private static final String FMT_COLDEF = "%1$s %2$s %3$s";
		private static final String FMT_NULL = "NULL";
		private static final String FMT_NOT_NULL = "NOT NULL";
		
		// field typing 
		public static final int AUTOKEY = 0;
		public static final int INTEGER = 1;
		public static final int FLOAT = 2;
		public static final int TEXT = 3;
		public static final int DATE = 4;
		public static final int TIME = 5;
		public static final int DATETIME = 6;
		
		public static final String COLUN_DEF = "";
		
		// meta data for column
		public String name = null;
		public String colType = null;
		public int type = 0;
		public int length = 0;
		public String deflt = null;
		public String fmtStr = null;
		
		/** parsing constructor */
		public ColMeta(String fld) {
			tmfmt = new SimpleDateFormat(FMT_TIME);
			dtfmt = new SimpleDateFormat(FMT_DATE);
			parse(fld);
		}
		
		/**
		 * (weak at best) polymorphic formatter.
		 * @param val
		 * @return
		 */
		public String formatData(Object val) {
			String data = null;
			switch(type){
				case AUTOKEY: 
				case INTEGER:
					data = String.format(fmtStr,((Integer) val).intValue());
					break;
				case TEXT:
					data = String.format(fmtStr,val.toString());
					break;
				case FLOAT:
					data = String.format(fmtStr, ((Float) val).floatValue());
					break;
				case DATE:
					data = String.format(fmtStr, dtfmt.format((Date)val));
					break;
				case TIME:
					data =String.format(fmtStr, tmfmt.format((Date)val));
					break;
				case DATETIME:
					data = String.format(fmtStr,dtfmt.format((Date)val),tmfmt.format((Date)val));
					break;
			}
			
			// return data formatted for SQL
			return data;
		}
		
		/**
		 * Build the column line for the 
		 * table create statement.
		 * 
		 * @return
		 */
		public String formatDefinition(){
			String np = null;
			
			// figure out the defaults and NULL allowance
			if (type==AUTOKEY)
				np = "";
			else
				np = deflt.equals(FMT_NULL) ? FMT_NULL : FMT_NOT_NULL;
			
			// send it along...
			return String.format(FMT_COLDEF, name, colType, np); 
		}
		
		/**
		 * Parse field format string from 
		 * database.xml
		 *   ==>  name::type::default
		 * 
		 * code below pretty much defines 
		 * the type translations...
		 * 
		 * @param fld
		 */
		public void parse(String fld) {
			String [] parts = fld.split("::");
			//logger.info("fld " + fld);

			name = parts[0];
			colType = parts[1].toLowerCase();
			deflt = parts[2];
			                
			if ("autokey".equals(colType)){
				// sqlite auto increment type	
				type = AUTOKEY;
				colType = FMT_AUTOKEY;
				fmtStr = FMT_NUMBER;
			}
			else if (colType.startsWith("varchar")) {
				type = TEXT;
				fmtStr = FMT_STRING;
				length = Integer.parseInt(parts[1].substring(7));
				colType = colType.replaceAll(SIZED_FIELD_PARSE, SIZED_FIELD_FORMAT);
			}
			else if (colType.startsWith("char") || "character".equals(colType)) {
				type = TEXT;
				fmtStr = FMT_STRING;
				length = Integer.parseInt(parts[1].substring(7));				
				colType = colType.replaceAll(SIZED_FIELD_PARSE, SIZED_FIELD_FORMAT);
			}
			else if ("date".equals(colType)) {
				type = DATE;
				fmtStr = FMT_DATE;
			}
			else if ("time".equals(colType)) {
				type = TIME;
				fmtStr = FMT_TIME;
			}
			else if ("datetime".equals(colType) || "timestamp".equals(colType)) {
				type = DATETIME;
				fmtStr = FMT_DATETIME;
				colType = "datetime";
			}
			else if ("text".equals(colType)) {
				type = TEXT;
				fmtStr = FMT_STRING;
			}
			else if ("int".equals(colType) || "integer".equals(colType) || "numeric".equals(colType)) {
				type = INTEGER;
				fmtStr = FMT_NUMBER;
				colType = "integer";
			}
			else if ("float".equals(colType) || "decimal".equals(colType)   || "double".equals(colType)) {
				type = FLOAT;
				fmtStr = FMT_DECIMAL;
			}
		}
	}

	public ContentValues getDefaults(){
		ContentValues cv = new ContentValues();
		for (int i=0;i<rowMeta.length;i++){
			cv.put(rowMeta[i].name, rowMeta[i].deflt);
		}
		return cv;
	}
	
	public String [] getColumnNames(){
		String [] rv = new String [colNdx.size()];
		Iterator<String> itr = colNdx.iterator();
		int ndx = 0;
		try {
			while(itr.hasNext())
				rv[ndx++] = itr.next();
		}
		catch(Exception ex) {
			logger.error("String Array Error on field " + String.valueOf(ndx),ex);
		}
		
		return rv;
		}

	/**
	 * Usable constructor.
	 * 
	 * Note: Does not 'keep' a ref
	 * to resources...
	 * 
	 * @param res
	 * @param entityRef
	 */
	public Context(String [] res){
		props = res;
		entity = props[0];
		buildMeta();
	}
		
	/** 
	 * Read in the database definitions for 
	 * this context
	 */
	private void buildMeta() {		
		// build meta row
		rowMeta = new ColMeta [props.length-1];
		colNdx = new ArrayList<String>();
		
		// populate meta row with column data
		// and index the col by name
		for(int i=1;i<props.length;i++){
			rowMeta[i-1] = new ColMeta(props[i]);
			colNdx.add(rowMeta[i-1].name);
		}
	}
	
	//----  column meta data accessors
	
	/**
	 * Return column meta data
	 */
	public ColMeta getColumn(String nm) {
		return rowMeta[colNdx.indexOf(nm)];
	}

	/**
	 * Get  
	 * @param nm
	 * @return
	 */
	public int getColType(String nm) {
		return getColumn(nm).type;
	}
	
	//--------------   DDL  ----------------
	/**
	 * Format the create DDL for this entity
	 */
	public String create(){
		// build col list
		StringBuilder cols = new StringBuilder();
		
		// iterate over meta data for defs
		for (int i = 0; i < rowMeta.length; i++) {
			if(cols.length() > 0)
				cols.append(",\n");
			cols.append(rowMeta[i].formatDefinition());
		}
		// return DDL
		return String.format(DDL_CREATE,entity,cols.toString());
	}
	
	/**
	 * Format a drop DDL statement for this entity
	 */
	public String drop(){
		return String.format(DDL_DROP,entity);
	}

	//--------------   SQL  ----------------
	/**
	 * Format a select for this entity
	 * 
	 * @param cols
	 * @param fltr
	 * @param grpby
	 * @param hvg
	 * @param ordby
	 * @param distinct
	 * @return
	 */
	public String select(String [] cols, ContentValues fltr, String [] grpby, ContentValues hvg, String [] ordby) {
		return select(cols,fltr,grpby,hvg,ordby,false);
	}
	
	public String select(String [] cols, ContentValues fltr, String [] grpby, ContentValues hvg, String [] ordby, boolean distinct) {
		String colList = null;
		String where = null;
		String groupby = null;
		String having = null;
		String orderby = null;
		String dist = distinct ? "Distinct" : "";
		
		// default to all columns in list
		colList = (cols != null ? Util.join(cols,",") : Util.join(getColumnNames(),","));
		
		// add where clause	
		where = (fltr == null ? "" : " Where " +buildClause(fltr," and "));
		
		// add group by clause
		groupby = (grpby == null ? "" : " Group By " + Util.join(grpby,","));
		
		// add having clause
		having = (hvg == null ? "" : " Having " + buildClause(hvg," and "));
		
		orderby = (ordby == null ? "" : " Order By " + Util.join(ordby,","));

		// ready for the big format...
		return String.format(SQL_SELECT,dist,colList,entity,where,groupby,having,orderby);
	}
	
	/**
	 * Format an update sql statement for this entity
	 */
	public String update(ContentValues vals, ContentValues fltr) {
		// return resources.getString(SQL_UPDATE, entity,(fltr == null ? "" : " Where " + buildClause(fltr, " and ")));
		return null;
	}

	/**
	 * Format a delete sql statement for this entity
	 */
	public String delete(ContentValues fltr){
		// return resources.getString(R.string.delete, entity, (fltr == null ? "" : " Where " + buildClause(fltr, " and ")));
		return null;
	}

	/**
	 * Format an insert for this entity
	 * 
	 * Note, not yet implemented...
	 */
	public String insert(ContentValues vals){
		return null;
	}

	/**
	 * Return table/proc/view/ name
	 * @return
	 */
	public String getEntityName(){
		return entity;
	}
		
	/**
	 * Build a sub clause using 
	 * default comparator.
	 * 
	 * @param vals
	 * @return
	 */
	public String buildClause(ContentValues vals, String delimitter) {
		return buildClause(vals,delimitter,DEFAULT_COMP);
	}
	
	/**
	 * Build where clause checking keys 
	 * for comparators and using comp if
	 * none found.  Comparators are specified
	 * in the key by formatting it as 
	 * fldname_comparator (as opposed to simply
	 * fldname). Each phrase of the clause is 
	 * separated by the delimiter. 
	 * 
	 * @param vals
	 * @param delimitter
	 * @param comp
	 * @return
	 */
	public String buildClause(ContentValues vals, String delimiter,String comp) {
		StringBuilder clause = new StringBuilder();
		String left = null;
	
		// get an set to iterate 
		Set<Entry<String,Object>> s = vals.valueSet();
		Iterator<Entry<String,Object>> itr = s.iterator();
		while(itr.hasNext()){
			Entry<String,Object> e = itr.next();
			String colnm = null;
			String cmp = null;
	
			String work = (String)e.getKey();
			
			// detect comparator  and set left side
			String vps [] = ((String)e.getKey()).split("_");
			
			for(int i=0;i<vps.length;i++)
				//logger.debug("VPS:" + vps[i]);
			
			switch(vps.length){
				case 1:
					colnm = vps[0];
					cmp = comp;
					break;
				case 2:
					colnm = work.startsWith("_") ? vps[1] : vps[0];
					cmp =  work.startsWith("_") ? comp : vps[1];
					break;
				default:
					colnm = vps[1];
					cmp = vps[2];
			}
			
			// put left side together
			colnm = work.startsWith("_") ? "_" + colnm : colnm;
			left = String.format("%1$s %2$s ", colnm, cmp);

			// get right side
			ColMeta col = getColumn(colnm);
			String right = String.format(col.fmtStr,e.getValue());
			
			// check for delimitter
			if (clause.length() > 0)
				clause.append(delimiter);
			
			// add phrase
			clause.append(left).append(right);
		}
		return clause.toString();
	}
}

